package sqlite

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"sync"

	_ "github.com/mattn/go-sqlite3"
)

type sqliteDB struct {
	mu sync.Mutex

	*sql.DB
	attachments
}

type attachments struct {
	tx *sql.Tx

	table     string
	where     string
	whereArgs []interface{}

	limit   int
	offset  int
	groupby string
	orderby string
}

func NewSqliteDB(path string) (Operator, error) {
	db, err := sql.Open("sqlite3", "file:"+path+"?_loc=auto&_encoding=UTF-8")
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %v", err)
	}

	// 测试连接是否正常
	if err = db.Ping(); err != nil {
		db.Close()
		return nil, fmt.Errorf("failed to ping database: %v", err)
	}

	return &sqliteDB{DB: db}, nil
}

func (db *sqliteDB) Table(table string) Operator {
	db.mu.Lock()
	defer db.mu.Unlock()

	newAttach := db.attachments
	newAttach.table = table
	return &sqliteDB{
		DB:          db.DB,
		attachments: newAttach,
	}
}

func (db *sqliteDB) Where(where string, args ...interface{}) Operator {
	db.where = where
	db.whereArgs = args
	return db
}

func (db *sqliteDB) Limit(limit int) Operator {
	db.limit = limit
	return db
}

func (db *sqliteDB) Offset(offset int) Operator {
	db.offset = offset
	return db
}

func (db *sqliteDB) GroupBy(groupby string) Operator {
	db.groupby = groupby
	return db
}

func (db *sqliteDB) OrderBy(orderby string) Operator {
	db.orderby = orderby
	return db
}

func (db *sqliteDB) Query(fields ...string) (result []map[string]interface{}, err error) {
	// 组装SQL语句
	sSql := strings.Join(append([]string{},
		"SELECT",
		strings.Join(fields, ","),
		"FROM",
		db.table,
	), " ")
	db.prepareArgs(&sSql)

	// 查询
	var rows *sql.Rows
	if db.tx != nil {
		rows, err = db.tx.Query(sSql, db.whereArgs...)
	} else {
		rows, err = db.DB.Query(sSql, db.whereArgs...)
	}
	if err != nil {
		return result, err
	}
	defer rows.Close()

	// 获取列信息
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	colCount := len(columns)

	// 解析查询结果
	for rows.Next() {
		// 创建值的切片和指针切片
		values := make([]interface{}, colCount)
		valuePtrs := make([]interface{}, colCount)

		for i := range columns {
			valuePtrs[i] = &values[i]
		}

		// 扫描数据到指针切片
		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, err
		}

		// 创建当前行的map
		rowMap := make(map[string]interface{})

		for i, col := range columns {
			// 处理可能的NULL值
			val := values[i]
			b, ok := val.([]byte)
			if ok {
				// 如果是[]byte，转换为string
				rowMap[col] = string(b)
			} else {
				rowMap[col] = val
			}
		}
		result = append(result, rowMap)
	}

	if rows.Err() != nil {
		return result, rows.Err()
	}
	return result, nil
}

func (db *sqliteDB) Insert(fieldWithArg map[string]interface{}) error {
	var (
		fields = make([]string, 0, len(fieldWithArg))
		values = make([]string, 0, len(fieldWithArg))
		args   = make([]interface{}, 0, len(fieldWithArg))
	)

	for field, arg := range fieldWithArg {
		fields = append(fields, field)
		values = append(values, "?")
		args = append(args, arg)
	}

	// 组装SQL语句
	sSql := strings.Join(append([]string{},
		"INSERT INTO",
		db.table,
		"("+strings.Join(fields, ",")+")",
		"VALUES",
		"("+strings.Join(values, ",")+")",
	), " ")

	// 执行
	_, err := db.Exec(sSql, append(args, db.whereArgs...)...)
	return err
}

func (db *sqliteDB) Update(fieldWithArg map[string]interface{}) error {
	var (
		fields = make([]string, 0, len(fieldWithArg))
		args   = make([]interface{}, 0, len(fieldWithArg))
	)

	for field, arg := range fieldWithArg {
		fields = append(fields, field+" = ?")
		arg = append(args, arg)
	}

	// 组装SQL语句
	sSql := strings.Join(append([]string{},
		"UPDATE",
		db.table,
		"SET",
		strings.Join(fields, ","),
	), " ")

	// 执行
	_, err := db.Exec(sSql, append(args, db.whereArgs...)...)
	return err
}

func (db *sqliteDB) Delete() error {
	// 组装SQL语句
	sSql := strings.Join(append([]string{},
		"DELETE FROM",
		db.table,
	), " ")

	// 执行
	_, err := db.Exec(sSql, db.whereArgs...)
	return err
}

func (db *sqliteDB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sqliteDB, error) {
	tx, err := db.DB.BeginTx(ctx, opts)
	if err != nil {
		return nil, err
	}
	return &sqliteDB{
		DB: db.DB,
		attachments: attachments{
			tx: tx,
		},
	}, err
}

func (db *sqliteDB) WithTransaction(ctx context.Context, opts *sql.TxOptions, fn func(tx Operator) error) error {
	db, err := db.BeginTx(ctx, opts)
	if err != nil {
		return err
	}

	defer func() {
		if p := recover(); p != nil {
			db.tx.Rollback()
			panic(p) // 重新抛出panic
		} else if err != nil {
			db.tx.Rollback()
		} else {
			err = db.tx.Commit()
		}
	}()

	return fn(db)
}

func (db *sqliteDB) Exec(sSql string, args ...interface{}) (sql.Result, error) {
	db.prepareArgs(&sSql)

	if db.tx != nil {
		return db.tx.Exec(sSql, args...)
	}
	return db.DB.Exec(sSql, args...)
}

func (db *sqliteDB) ExecContent(fileContent []byte) error {
	queries := strings.Split(string(fileContent), ";\n")
	for _, query := range queries {
		query = strings.TrimSpace(query)
		if query == "" {
			continue
		}
		if db.tx != nil {
			if _, err := db.tx.Exec(query); err != nil {
				return fmt.Errorf("execute query from %q\nError: %w", query, err)
			}
		} else {
			if _, err := db.Exec(query); err != nil {
				return fmt.Errorf("execute query from %q\nError: %w", query, err)
			}
		}
	}
	return nil
}

func (db *sqliteDB) Close() error {
	return db.DB.Close()
}

// 组装语句的args
func (db *sqliteDB) prepareArgs(sSql *string) {
	if len(db.where) > 0 {
		*sSql += " WHERE " + db.where
	}
	if db.limit > 0 {
		*sSql += fmt.Sprintf(" LIMIT %d", db.limit)
	}
	if db.offset > 0 {
		*sSql += fmt.Sprintf(" OFFSET %d", db.offset)
	}
	if len(db.groupby) > 0 {
		*sSql += " GROUP BY " + db.groupby
	}
	if len(db.orderby) > 0 {
		*sSql += " ORDER BY " + db.orderby
	}
}
